package com.laxcen.smoke.dao;

import com.laxcen.smoke.dto.res.AddBoxExtraRes;
import com.laxcen.smoke.dto.res.SmartRes;
import com.laxcen.smoke.entity.AbNormalRecord;
import com.laxcen.smoke.entity.Smart;
import com.laxcen.smoke.entity.SmartBoard;
import com.laxcen.smoke.entity.SmartBox;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

@Mapper
@Repository
public interface IntelligentMonitorDao {

    @Insert("insert into smart(id,no,devicename,lat,lng,address,status,createtime,remark,password,updatetime) values(#{id},#{no},#{device},#{lat},#{lng},#{address},#{status},#{createtime},#{remark},#{password},#{updatetime})")
    int insert(String id, String no, String device, BigDecimal lat, BigDecimal lng, String address, int status, Date createtime, String remark, String password, Date updatetime);

    @Insert("insert into abnormalrecord(id,smartno,abnormaltype,createtime,state) values(#{id},#{smartno},#{abnormaltype},#{createtime},#{state})")
    int insertAbnor(String id, String smartno, int abnormaltype, Date createtime, int state);

    @Insert("insert into smartboard(id,specification,no,upsstatus,upselectricity,status,createtime,remark) values(#{id},#{specification},#{no},#{upsstatus},#{upselectricity},#{status},#{createtime},#{remark})")
    int insertsmartbo(String id, int specification, String no, int upsstatus, double upselectricity, int status, Date createtime, String remark);

    @Insert("insert into real_smart_smartboard values(#{id},#{no},#{smartid},#{boardid},#{createtime})")
    int insertrealsmartandsboard(String id, String no, String smartid, String boardid, Date createtime);

    @Insert("insert into smartbox(id,no,status,createtime,remark) values(#{id},#{no},#{status},#{createtime},#{remark})")
    int insertsmartbox(String id, String no, int status, Date createtime, String remark);

    @Insert("insert into real_board_box values(#{id},#{no},#{boardid},#{boxid},#{createtime})")
    int insertrealboandbox(String id, String no, String boardid, String boxid, Date createtime);

    @Delete("delete from real_smart_smartboard where smartid = #{id}")
    int delete(String id);

    @Delete("delete from smart where id = #{id}")
    int deleteSmart(String id);

    @Delete("delete from real_smart_smartboard where boardid = #{id}")
    int deleteBoardone(String id);

    @Delete("delete from real_board_box where boardid = #{id}")
    int deleteBoardtwo(String id);

    @Delete("delete from real_board_box where boxid = #{id}")
    int deletebBox(String id);

    @Update("update smart set no = #{no},devicename = #{device},lat = #{lat},lng = #{lng},address = #{address},remark = #{remark} where id = #{id}")
    int update(String id, String no, String device, BigDecimal lat, BigDecimal lng, String address, String remark);

    @Update("update smartboard set specification = #{specification},no = #{no}, remark = #{remark} where id = #{id}")
    int updatesmbod(String id, int specification, String no, String remark);

    @Delete("delete from smartboard where id = #{id}")
    int deletesmbod(String id);

    @Update("update smart set status = #{status} where no = #{smartno}")
    int updateSmartByNo(int status, String smartno);

    @Update("update smartboard sbd right join real_smart_smartboard rss on sbd.id = rss.boardid right join smart sm on sm.id = rss.smartid set sbd.status = #{status} where  sm.no = #{smartno} and sbd.specification = 0")
    int updateSmartBoardByNo(int status, String smartno);


    @Update("update smartboard set status = #{status} where id = #{id}")
    int updatesmartBoardBy(int status, String smartno, String id);

    @Update("update smartboard set UpsStatus = #{upsstatus},UpsElectricity = #{upselectricity} where id = #{id}")
    int updateUpsForSmart(int upsstatus, String upselectricity, String id);

    @Update("update smartbox sb right join real_board_box rbb on rbb.boxid = sb.id set sb.status = #{status} where rbb.no = #{boxonlyno}")
    int updateBoxStatus(int status, String boxonlyno);

    @Update("update smartboard sbd right join real_board_box rbb on rbb.boardid = sbd.id set sbd.status = #{status} where rbb.no = #{boxonlyno}")
    int updateBoardStatus(int status, String boxonlyno);

    @Update("update smart sm right join real_smart_smartboard rss on sm.id = rss.SmartId left join  smartboard sbd on rss.boardid = sbd.id right join real_board_box rbb on rbb.boardid = sbd.id  set sm.status = #{status} where rbb.no = #{boxonlyno}")
    int updateSmartStatus(int status, String boxonlyno);

    @Select("select a.id from (select sb.id from smartboard sb right join  real_smart_smartboard rss  on rss.BoardId = sb.id left join smart sm on rss.SmartId = sm.id where sm.no = #{smartno})a")
    List<String> getSmartboardidBySmartNo(String smartno);

    @Select("select distinct sbd.id from smartbox sbox left join real_board_box rbb on sbox.id = rbb.BoxId left join smartboard sbd on rbb.BoardId = sbd.id left join real_smart_smartboard rss on rbb.BoardId = rss.BoardId left join smart sm on rss.SmartId = sm.id   where sbox.status <> #{status} and sm.no = #{smartno}  ")
    List<String> getIdBySmartno(String smartno, int status);

    @Select("select * from smart where id = #{id}")
    Smart getSmartById(String id);

    @Select("select distinct sm.no from smart sm right join real_smart_smartboard rss on sm.id = rss.smartid where sm.no <> #{smartno}")
    List<String> getSmartNoList(String smartno);

    @Select("select distinct sm.* from smart sm right join real_smart_smartboard rss on sm.id = rss.SmartId  where sm.no = #{smartno}")
    Smart getSmartByNo(String smartno);

    @Select("select * from smartboard where id = #{id} and status = 0")
    SmartBoard getSmartBoardById(String id);

    @Select("select distinct sm.*,smb.upsstatus upsstatus,smb.upselectricity upselectricity from real_smart_smartboard rss left join smart sm on sm.id = rss.SmartId left join smartboard smb on smb.id = rss.BoardId  where sm.no = #{no} and sm.createtime between #{starttime} and #{endtime} order by sm.status desc")
    List<SmartRes> getByAllList(String no, Date starttime, Date endtime);

    @Select("select distinct sm.*,smb.upsstatus upsstatus,smb.upselectricity upselectricity,iss.versionno versionno from real_smart_smartboard rss left join smart sm on sm.id = rss.SmartId left join smartboard smb on smb.id = rss.BoardId left join issue iss on sm.versionid = iss.id order by sm.status desc")
    List<SmartRes> getAllList();

    @Select("select distinct sm.*,smb.upsstatus upsstatus,smb.upselectricity upselectricity,iss.versionno versionno from real_smart_smartboard rss left join smart sm on sm.id = rss.SmartId left join smartboard smb on smb.id = rss.BoardId left join issue iss on sm.versionid = iss.id where sm.no in (select distinct sm.no from smart sm right join real_smart_smartboard rss on sm.id = rss.SmartId where sm.address like CONCAT('%',#{place},'%')) order by sm.status desc")
    List<SmartRes> getAllListByRole(String place);

    @Select("select * from abnormalrecord where id = #{id}")
    AbNormalRecord getAbnById(String id);

    @Select("select sbox.id from smartbox sbox left join real_board_box rbb on sbox.id = rbb.BoxId left join real_smart_smartboard rss on rbb.BoardId = rss.BoardId left join smart sm on rss.SmartId = sm.id   where sbox.status = #{status} and sm.no = #{smartno} ")
    List<String> getNotState(String smartno, int status);

    @Select("select distinct sm.no from smart sm right join real_smart_smartboard rss on sm.id = rss.smartid ")
    List<String> getAbn();

    @Select("select distinct sm.*,smb.upsstatus upsstatus,smb.upselectricity upselectricity,iss.versionno versionno from real_smart_smartboard rss left join smart sm on sm.id = rss.SmartId left join smartboard smb on smb.id = rss.BoardId left join issue iss on sm.versionid = iss.id  where sm.no like CONCAT('%',#{searchtext},'%') or sm.devicename like CONCAT('%',#{searchtext},'%') or sm.address like CONCAT('%',#{searchtext},'%') order by sm.status desc")
    List<SmartRes> getAllListByTab(String searchtext);


    @Select("select distinct sm.*,smb.upsstatus upsstatus,smb.upselectricity upselectricity,iss.versionno versionno from real_smart_smartboard rss left join smart sm on sm.id = rss.SmartId left join smartboard smb on smb.id = rss.BoardId left join issue iss on sm.versionid = iss.id  where  sm.no in (select distinct sm.no from smart sm right join real_smart_smartboard rss on sm.id = rss.SmartId where sm.address like CONCAT('%',#{place},'%') and sm.no like CONCAT('%',#{searchtext},'%') or sm.devicename like CONCAT('%',#{searchtext},'%') or sm.address like CONCAT('%',#{searchtext},'%')  ) order by sm.status desc")
    List<SmartRes> getAllListByTabRole(String searchtext, String place);

    @Select("select distinct sm.*,smb.upsstatus upsstatus,smb.upselectricity upselectricity from real_smart_smartboard rss left join smart sm on sm.id = rss.SmartId left join smartboard smb on smb.id = rss.BoardId  where sm.no = #{no} order by sm.status desc")
    List<SmartRes> getByIdList(String no);

    @Select("select distinct sm.*,smb.upsstatus upsstatus,smb.upselectricity upselectricity from real_smart_smartboard rss left join smart sm on sm.id = rss.SmartId left join smartboard smb on smb.id = rss.BoardId order by sm.status desc ")
    List<SmartRes> getByNoneList();

    @Select("select distinct sm.*,smb.upsstatus upsstatus,smb.upselectricity upselectricity from real_smart_smartboard rss left join smart sm on sm.id = rss.SmartId left join smartboard smb on smb.id = rss.BoardId  where sm.createtime between #{starttime} and #{endtime} order by sm.status desc")
    List<SmartRes> getByTimeList(Date starttime, Date endtime);

    @Select("select distinct sm.*,smb.upsstatus upsstatus,smb.upselectricity upselectricity,iss.versionno versionno from real_smart_smartboard rss left join smart sm on sm.id = rss.SmartId left join smartboard smb on smb.id = rss.BoardId left join issue iss on sm.versionid = iss.id  where sm.id = #{id} order by sm.status desc")
    List<SmartRes> getByOneIdList(String id);

    @Select("select distinct sbd.*,sm.address address,rss.no boardonlyno from real_smart_smartboard rss left join smartboard sbd on rss.BoardId = sbd.Id left join smart sm on rss.smartid = sm.id where rss.boardid = #{id}")
    List<SmartBoard> getSBDList(String id);

    @Select("select distinct sbd.*,sm.address address,rss.no boardonlyno from real_smart_smartboard rss left join smartboard sbd on rss.BoardId = sbd.Id left join smart sm on rss.smartid = sm.id where rss.smartid = #{id}")
    List<SmartBoard> getSMBORDList(String id);

    @Select("select distinct sbox.*,rbb.no boxonlyno from real_board_box  rbb left join smartbox sbox on rbb.BoxId = sbox.Id left join smartboard sbd on rbb.BoardId = sbd.Id where sbd.id = #{id}")
    List<SmartBox> getSmartBoxList(String id);

    @Select("select boardid from real_smart_smartboard where smartid = #{id} ")
    String getboardid(String id);

    @Select("select id from real_board_box where boardid = #{id}")
    List<String> getBoxIsNotNull(String id);

    //查询智能柜下副柜数量id,格子编号
    @Select("select rss.boardid boardid,sbox.no boxno from real_smart_smartboard rss left join smart sm on rss.smartid = sm.id left join smartboard sbd on rss.boardid = sbd.id left join real_board_box rbb on sbd.id = rbb.boardid left join smartbox sbox on rbb.boxid = sbox.id  where sbd.Specification = #{specification} and sm.no = #{smartno}")
    List<AddBoxExtraRes> getFboardNum(int specification, String smartno);

//    //根据智能柜id查询devicename
//    @Select("select devicename from smart where id = #{smartid} and devicename is not null ")
//    String getSmartBySmartid(String smartid);

    //根据智能柜id查询smartno
    @Select("select no from smart where id = #{smartid} ")
    String getSmartBySmartid(String smartid);

    //查询重复的副柜编号
    @Select("select sbd.no from smartboard sbd right join real_smart_smartboard rss on sbd.id = rss.boardid left join smart sm on rss.smartid = sm.id  where sbd.specification = #{specification} and sm.no = #{smartno}")
    List<String> getBoardno(int specification, String smartno);

    //根据实体柜id查询智能柜编号
    @Select("select distinct sm.no from smart sm right join real_smart_smartboard rss on sm.id = rss.SmartId left join smartboard sb on rss.BoardId = sb.id where rss.BoardId = #{boardid}  ")
    String getSmartByBoardid(String boardid);

    //根据实体柜id查询实体柜编号
    @Select("select no from smartboard where id = #{id}")
    String getsmartboardno(String id);

    //根据实体柜id查询智能柜编号
    @Select("select distinct sm.no from smart sm right join real_smart_smartboard rss on sm.id = rss.SmartId left join smartboard sb on rss.BoardId = sb.id where rss.BoardId = #{boardid}  ")
    String getSmartNoByBoardid(String boardid);

    //根据格子id查询智能柜编号
    @Select("select distinct sm.no from smart sm right join real_smart_smartboard rss on sm.id = rss.SmartId left join smartboard sb on rss.BoardId = sb.id right join real_board_box rbb on sb.id = rbb.BoardId left join smartbox sbx on rbb.BoxId = sbx.id where rbb.BoxId = #{boxid} ")
    String getSmartByBoxid(String boxid);

}
